package cn.nwpu.eckel.Dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.nwpu.eckel.model.MPS;
import cn.nwpu.eckel.model.Plan;

import static cn.nwpu.eckel.utility.DBConnection.*;

public class MPSDao {
	public static List<MPS> getAllMPS(){
		List<MPS> mpses=new ArrayList<MPS>();
		MPS tempmps=null;
		ResultSet rs=null;
		Statement stmt=null;
		String sql="select * from mps_tb;";
		Connection con=getConnection();
		try{
			stmt=con.createStatement();
			rs=stmt.executeQuery(sql);
			while(rs.next()){
				tempmps=new MPS(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getString(5), rs.getDate(6),
						  rs.getInt(7),rs.getInt(8),rs.getInt(9),rs.getInt(10),rs.getInt(11),rs.getInt(12),rs.getInt(13));
				mpses.add(tempmps);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(stmt);
			close(con);
		}
		return mpses;
	}
	public static MPS getMPSById(int mpsNo){
		MPS mps=new MPS();
		Connection con=getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		String sql="select * from mps_tb where id=?";
		try {
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1,mpsNo);
			rs=pstmt.executeQuery();
			while(rs.next()){
				mps.id=rs.getInt(1);
				mps.pNo=rs.getInt(2);
				mps.pName=rs.getString(3);
				mps.pType=rs.getString(4);
				mps.planner=rs.getString(5);
				mps.ph=rs.getInt(7);
			    mps.lt=rs.getInt(8);
			    mps.stock=rs.getInt(9);
			    mps.safeStock=rs.getInt(10);
			    mps.lotsize=rs.getInt(11);
			    mps.rf=rs.getInt(12);
			    mps.sf=rs.getInt(13);
			    mps.p=new Plan[rs.getInt(7)+1];
			}	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(con);
		}
		return mps;
	}
	public static void insertMPS(MPS mps){
		Connection con=getConnection();
		PreparedStatement pstmt=null;
		String sql="insert into mps_tb values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1,mps.id);
			pstmt.setInt(2,mps.pNo);
			pstmt.setString(3,mps.pName);
			pstmt.setString(4,mps.pType);
			pstmt.setString(5,mps.planner);
			pstmt.setDate(6, mps.buildTime);
			pstmt.setInt(7,mps.ph);
			pstmt.setInt(8,mps.lt);
			pstmt.setInt(9,mps.stock);
			pstmt.setInt(10,mps.safeStock);
			pstmt.setInt(11,mps.lotsize);
			pstmt.setInt(12,mps.rf);
			pstmt.setInt(13,mps.sf);
			pstmt.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(con);
		}
	}
	public static void deleteMPS(int mpsNo){
		Connection con=getConnection();
		PreparedStatement pstmt_1=null;
		PreparedStatement pstmt_2=null;
		PreparedStatement pstmt_3=null;
		PreparedStatement pstmt_4=null;
		PreparedStatement pstmt_5=null;
		String sql1="delete from mps_tb where id=?";
		String sql2="delete from forecast_tb where mpsNo=?";
		String sql3="delete from order_tb where mpsNo=?";
		String sql4="delete from schedulereceipt_tb where mpsNo=?";
		String sql5="delete from plan_tb where mpsNo=?";
		try{
			pstmt_1=con.prepareStatement(sql1);
			pstmt_1.setInt(1,mpsNo);
			pstmt_1.executeUpdate();
			
			pstmt_2=con.prepareStatement(sql2);
			pstmt_2.setInt(1,mpsNo);
			pstmt_2.executeUpdate();
			
			pstmt_3=con.prepareStatement(sql3);
			pstmt_3.setInt(1,mpsNo);
			pstmt_3.executeUpdate();
			
			pstmt_4=con.prepareStatement(sql4);
			pstmt_4.setInt(1,mpsNo);
			pstmt_4.executeUpdate();
			
			pstmt_5=con.prepareStatement(sql5);
			pstmt_5.setInt(1,mpsNo);
			pstmt_5.executeUpdate();
			
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt_1);
			close(con);
		}
	}
	public static MPS alterMPS(int id,MPS newMps){
		MPS oldMPS=MPSDao.getMPSById(id);
		Connection con=getConnection();
		PreparedStatement pstmt=null;
		String sql="update mps_tb set pNo=?,pName=?,pType=?,planner=?,ph=?,lt=?,stock=?,safeStock=?,lotsize=?,rf=?,sf=? where id=?";
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1,newMps.pNo);
			pstmt.setString(2,newMps.pName);
			pstmt.setString(3,newMps.pType);
			pstmt.setString(4,newMps.planner);
			pstmt.setInt(5,newMps.ph);
			pstmt.setInt(6,newMps.lt);
			pstmt.setInt(7,newMps.stock);
			pstmt.setInt(8,newMps.safeStock);
			pstmt.setInt(9,newMps.lotsize);
			pstmt.setInt(10,newMps.rf);
			pstmt.setInt(11,newMps.sf);
			pstmt.setInt(12, id);
			pstmt.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(con);
		}
		return oldMPS;
	}

}
